﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace CMS_GYM
{
    class clsPhieuChiTienDAO
    {
        #region Attribute
        /// <summary>
        /// Kết nối SQL Server
        /// </summary>
        protected CSQLServer sqlServer = new CSQLServer();
        protected clsPhieuChiTienChiTietDAO PhieuChiChiTietDAO = new clsPhieuChiTienChiTietDAO();
        #endregion

        /// <summary>
        /// Lay bang phieu thu theo nhieu tieu chi
        /// </summary>
        /// <param name="TuNgay"></param>
        /// <param name="DenNgay"></param>
        /// <param name="TuSo"></param>
        /// <param name="DenSo"></param>
        /// <param name="KyHieu"></param>
        /// <param name="NguoiTaoId"></param>
        /// <param name="MaKhachHang"></param>
        /// <param name="TuKhoa"></param>
        /// <param name="LoaiPhieu"></param>
        /// <returns></returns>
        public DataTable LayBang(DateTime? TuNgay, DateTime? DenNgay, string TuSo, string DenSo, string KyHieu, int? NguoiTaoId, string MaKhachHang, string TuKhoa, string LoaiPhieu)
        {
            string sql = "spSearchPhieuGiaoDich";
            string[] ParameterColection = new string[] {    
                                    "@TuNgay","@DenNgay","@TuSo","@DenSo","@KyHieu","@NguoiTaoId"
                                    ,"@MaKhachHang","@TuKhoa","@LoaiPhieu"
                                };
            Object[] valueofParameter = new Object[] {
                                    TuNgay,DenNgay,TuSo,DenSo,KyHieu,
                                    NguoiTaoId,MaKhachHang,TuKhoa,LoaiPhieu
                                };
            DataTable table = sqlServer.readData(sql, ParameterColection, valueofParameter);
            return table;
        }

        /// <summary>
        /// Lay bang theo so phieu thu va ky hieu
        /// </summary>
        /// <param name="SoPhieuChi"></param>
        /// <param name="KyHieu"></param>
        /// <returns></returns>
        public DataTable LayBang(string SoPhieuChi, string KyHieu)
        {
            string sql = "spGetInfoPhieuChiTheoSoPhieuChiVaKyHieu";
            string[] ParameterColection = new string[] {    
                                    "@SoPhieuChi","@KyHieu"
                                };
            Object[] valueofParameter = new Object[] {
                                    SoPhieuChi,KyHieu
                                };
            DataTable table = sqlServer.readData(sql, ParameterColection, valueofParameter);
            return table;
        }

        public clsPhieuChiTienDTO LayThongMotPhieuChi(string SoPhieuChi, string KyHieu)
        {
            clsPhieuChiTienDTO PhieuChi = new clsPhieuChiTienDTO();
            DataTable table = LayBang(SoPhieuChi, KyHieu);
            if (table != null && table.Rows.Count > 0)
            {
                PhieuChi = ChuyenDoi(table.Rows[0]);
                if (PhieuChi != null)
                {
                    PhieuChi.CacPCCT = PhieuChiChiTietDAO.LayDanhSach(PhieuChi.PhieuChiId);
                    if (PhieuChi.CacPCCT == null)
                    {
                        return null;
                    }
                }
                else
                {
                    return null;
                }
                return PhieuChi;
            }
            else
            {
                return null;
            }
        }

        public DataTable LayBangPhieuChiChiet(string SoPhieuChi, string KyHieu)
        {
            return PhieuChiChiTietDAO.LayBang(SoPhieuChi, KyHieu);
        }

        public DataTable LayBangPhieuChiChiet(decimal PhieuChiId)
        {
            return PhieuChiChiTietDAO.LayBang(PhieuChiId);
        }


        private clsPhieuChiTienDTO ChuyenDoi(DataRow row)
        {
            clsPhieuChiTienDTO PhieuChi = new clsPhieuChiTienDTO();
            try
            {
                PhieuChi.PhieuChiId = decimal.Parse(row["PhieuChiId"].ToString());
                PhieuChi.SoPhieuChi = row["SoPhieuChi"] == null ? string.Empty : row["SoPhieuChi"].ToString();
                PhieuChi.KyHieu = row["KyHieu"] == null ? string.Empty : row["KyHieu"].ToString();
                PhieuChi.Ky = row["Ky"] == null ? string.Empty : row["Ky"].ToString();
                PhieuChi.NgayXuat = DateTime.Parse(row["NgayXuat"].ToString());
                PhieuChi.DoiTacId = row["DoiTacId"] == null || string.IsNullOrEmpty(row["DoiTacId"].ToString()) ? null : (decimal?)decimal.Parse(row["DoiTacId"].ToString());
                PhieuChi.MaDoiTac = row["MaDoiTac"] == null ? string.Empty : row["MaDoiTac"].ToString();
                PhieuChi.HoTen = row["HoTen"] == null ? string.Empty : row["HoTen"].ToString();
                PhieuChi.MaSoThue = row["MaSoThue"] == null ? string.Empty : row["MaSoThue"].ToString();
                PhieuChi.DiaChi = row["DiaChi"] == null ? string.Empty : row["DiaChi"].ToString();
                PhieuChi.PhanTramThue = row["PhanTramThue"] == null ? 0 : int.Parse(row["PhanTramThue"].ToString());
                PhieuChi.TongTienChi = double.Parse(row["TongTienChi"].ToString());
                PhieuChi.TongTienHang = double.Parse(row["TongTienHang"].ToString());
                PhieuChi.TongTienHang = double.Parse(row["TongTienThue"].ToString());
                PhieuChi.GhiChu = row["GhiChu"] == null ? string.Empty : row["GhiChu"].ToString();
                PhieuChi.NguoiTaoId = int.Parse(row["NguoiTaoId"].ToString());
                PhieuChi.NgayTao = DateTime.Parse(row["NgayTao"].ToString());
                PhieuChi.NguoiChinhSuaId = row["NguoiChinhSuaId"] == null || string.IsNullOrEmpty(row["NguoiChinhSuaId"].ToString()) ? null : (int?)int.Parse(row["NguoiChinhSuaId"].ToString());
                PhieuChi.NgayChinhSua = row["NgayChinhSua"] == null || string.IsNullOrEmpty(row["NgayChinhSua"].ToString()) ? null : (DateTime?)DateTime.Parse(row["NgayChinhSua"].ToString());
                PhieuChi.TrangThai = row["TrangThai"] == null ? "ACTIVE" : row["TrangThai"].ToString();
                return PhieuChi;
            }
            catch (Exception)
            {
                return null;
            }
        }

        /// <summary>
        /// Thêm thông tin Phiếu Xuất Bán Sỉ
        /// </summary>
        /// <param name="PhieuXuat">
        /// MaPhieuXuat                  nvarchar(10)
        /// NgayXuat                       smalldatetime
        /// MaNhanVienBanHang     nvarchar(10)
        /// TongTien                        float
        /// DaTra                             float
        /// DoiTac                     nvarchar(255)
        /// LoaiPhieuXuat                 nvarchar(100)
        /// TrangThai                       int
        /// </param>
        public int Them(clsPhieuChiTienDTO PhieuChi)
        {
            int i = -1;
            string sql = "spInsertPhieuChi";
            string[] ParameterColection = new string[] {    
                                    "@SoPhieuChi","@KyHieu","@Ky","@NgayXuat","@DoiTacId","@MaDoiTac"
                                    ,"@HoTen","@MaSoThue","@DiaChi","@NguoiNhan","@PhanTramThue","@TongTienChi","@TongTienHang"
                                    ,"@TongTienThue","@GhiChu","@NguoiTaoId"
                                };
            Object[] valueofParameter = new Object[] {
                                    PhieuChi.SoPhieuChi,PhieuChi.KyHieu,PhieuChi.Ky,PhieuChi.NgayXuat,PhieuChi.DoiTacId,PhieuChi.MaDoiTac,
                                    PhieuChi.HoTen,PhieuChi.MaSoThue, PhieuChi.DiaChi,PhieuChi.NguoiNhan,PhieuChi.PhanTramThue, PhieuChi.TongTienChi,PhieuChi.TongTienHang,
                                    PhieuChi.TongTienThue, PhieuChi.GhiChu,PhieuChi.NguoiTaoId
                                };
            i = sqlServer.writeData(sql, ParameterColection, valueofParameter);
            if (i != -1)
            {
                PhieuChi.PhieuChiId = i;
                for (int k = 0; k < PhieuChi.CacPCCT.Count; k++)
                {
                    PhieuChi.CacPCCT[k].PhieuChiId = PhieuChi.PhieuChiId;
                    if (PhieuChiChiTietDAO.Them(PhieuChi.CacPCCT[k]) == -1)
                    {
                        return -1;
                    }
                }
            }
            return i;
        }

        /// <summary>
        /// Sua phieu chi
        /// </summary>
        /// <param name="PhieuChi"></param>
        /// <returns></returns>
        public int Sua(clsPhieuChiTienDTO PhieuChi)
        {
            int i = -1;
            string sql = "spUpdatePhieuChi";
            string[] ParameterColection = new string[] {    
                                    "@PhieuChiId","@SoPhieuChi","@KyHieu","@Ky","@NgayXuat","@DoiTacId","@MaDoiTac"
                                    ,"@HoTen","@MaSoThue","@DiaChi","@PhanTramThue","@TongTienChi","@TongTienHang"
                                    ,"@TongTienThue","@GhiChu","@NguoiChinhSuaId","@NgayChinhSua"
                                };
            Object[] valueofParameter = new Object[] {
                                     PhieuChi.PhieuChiId,PhieuChi.SoPhieuChi,PhieuChi.KyHieu,PhieuChi.Ky,PhieuChi.NgayXuat,PhieuChi.DoiTacId,PhieuChi.MaDoiTac,
                                    PhieuChi.HoTen,PhieuChi.MaSoThue, PhieuChi.DiaChi,PhieuChi.PhanTramThue, PhieuChi.TongTienChi,PhieuChi.TongTienHang,
                                    PhieuChi.TongTienThue, PhieuChi.GhiChu,PhieuChi.NguoiChinhSuaId, PhieuChi.NgayChinhSua
                                };
            i = sqlServer.writeData(sql, ParameterColection, valueofParameter);
            if (i != -1)
            {
                for (int k = 0; k < PhieuChi.CacPCCT.Count; k++)
                {
                    if (PhieuChiChiTietDAO.Sua(PhieuChi.CacPCCT[k]) == -1)
                    {
                        return -1;
                    }
                }
            }
            return i;
        }

        /// <summary>
        /// Xoa mot phieu thu 
        /// </summary>
        /// <param name="PTCT"></param>
        /// <returns></returns>
        public int Xoa(decimal PhieuChiId)
        {
            int i = -1;
            string sql = "spDeletePhieuChi";
            string[] ParameterColection = new string[] { "@PhieuChiId" };
            Object[] valueofParameter = new Object[] { PhieuChiId };
            if (PhieuChiChiTietDAO.Xoa(PhieuChiId) == -1)
            {
                return -1;
            }
            else
            {
                i = sqlServer.writeData(sql, ParameterColection, valueofParameter);
            }
            return i;
        }

        /// <summary>
        /// Xoa tam thoi mot phieu thu 
        /// </summary>
        /// <param name="PTCT"></param>
        /// <returns></returns>
        public int XoaTamThoi(decimal PhieuChiId)
        {
            int i = -1;
            string sql = "spDeletePhieuChiTheoMaPhieuChi";
            string[] ParameterColection = new string[] { "@PhieuChiId" };
            Object[] valueofParameter = new Object[] { PhieuChiId };
            i = sqlServer.writeData(sql, ParameterColection, valueofParameter);
            return i;
        }

        /// <summary>
        /// Xoa tam thoi mot phieu thu 
        /// </summary>
        /// <param name="PTCT"></param>
        /// <returns></returns>
        public int XoaTamThoi(string SoPhieuChi, string KyHieu)
        {
            int i = -1;
            string sql = "spDeletePhieuChiTheoSoPhieuChi";
            string[] ParameterColection = new string[] { "@SoPhieuChi", "@KyHieu" };
            Object[] valueofParameter = new Object[] { SoPhieuChi, KyHieu };
            i = sqlServer.writeData(sql, ParameterColection, valueofParameter);
            return i;
        }

        public bool KiemTraTonTai(string SoPhieuChi, string KyHieu)
        {
            // Tạo danh sách "Tên các Parameter" đầu vào để thực hiện store
            string[] Parameters = new string[] { "@SoPhieuChi", "@KyHieu" };
            // Gán giá trị cho các Parameter" vừa được tạo ở trên
            object[] ValueOfParameters = new object[] { SoPhieuChi, KyHieu };
            // Gọi thực hiện lấy danh sách phiếu thu thu tiền 
            DataTable result = sqlServer.readData("spGetInfoPhieuChiTheoSoPhieuChiVaKyHieu", Parameters, ValueOfParameters);
            //Kiểm tra số dòng đọc được trong database
            if (result.Rows.Count > 0)
            {
                //phiếu thu này tồn tại trong CSDL
                return true;
            }
            else
            {
                //phiếu thu này không tồn tài trong CSDL
                return false;
            }
        }

        public DataTable ThongKeChiPhiTheoLoaiDinhKhoanCo(DateTime? TuNgay, DateTime? DenNgay)
        {
            string sql = "spGetBangThongKeChiPhiTheoDinhKhoanCo";
            string[] ParameterColection = new string[] { "@TuNgay", "@TuDen" };
            Object[] valueofParameter = new Object[] { TuNgay, DenNgay };
            DataTable table = sqlServer.readData(sql, ParameterColection, valueofParameter);
            return table;
        }
    }
}
